﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using CmuCertificates.Database;
using System.Data;

namespace CmuCertificates
{
    class DataAsset
    {
        // Declare variable
        private CMUCertificateManagerEntities db = new CMUCertificateManagerEntities();

        #region Read excel file
        public IWorkbook readExcelFile(string path)
        {
            using (FileStream fileToRead = new FileStream(path, FileMode.Open, FileAccess.ReadWrite))
            {
                try
                {
                    // readExcelFileAbove2007
                    XSSFWorkbook fileExcelToRead = new XSSFWorkbook(fileToRead);
                    return fileExcelToRead;
                }
                catch
                {
                    //readExcelFileBelow2007
                    HSSFWorkbook fileExcelToRead = new HSSFWorkbook(fileToRead);
                    return fileExcelToRead;
                }
            }
        }
        #endregion

        #region LinQ Query
        // Select All
        public IQueryable<Certificate> SelectAll_Certificate()
        {
            return from certificate in db.Certificates
                   select certificate;
        }
        public IQueryable<Class> SelectAll_Class()
        {
            return from lop in db.Classes
                   select lop;
        }
        public IQueryable<Course> SelectAll_Courses()
        {
            return from course in db.Courses
                   select course;
        }
        public IQueryable<Lecturer> SelectAll_Lecturer()
        {
            return from lecturer in db.Lecturers
                   select lecturer;
        }
        public IQueryable<Student> SelectAll_Student()
        {
            return from student in db.Students
                   select student;
        }
        public IQueryable<Year> SelectAll_Year()
        {
            return from year in db.Years
                   select year;
        }

        // Select based on equal condition
        public IQueryable<Certificate> Select_Certificate(string certificateID, string studentID, string courseID, string Score)
        {
            return from certificate in db.Certificates
                   where ((certificate.CertificateID == certificateID || certificateID == GlobalVar.KITURONG_QUERY)
                        && (certificate.StudentID == studentID || studentID == GlobalVar.KITURONG_QUERY)
                        && (certificate.CourseID == courseID || courseID == GlobalVar.KITURONG_QUERY)
                        && (certificate.Score == Score || Score == GlobalVar.KITURONG_QUERY))
                   select certificate;
        }
        public IQueryable<Class> Select_Class(string classID, string className, string classYear)
        {
            return from lop in db.Classes
                   where ((lop.ClassID == classID || classID == GlobalVar.KITURONG_QUERY)
                        && (lop.ClassName == className || className == GlobalVar.KITURONG_QUERY)
                        && (lop.ClassYear == classYear || classYear == GlobalVar.KITURONG_QUERY))
                   select lop;
        }
        public IQueryable<Course> Select_Courses(string courseID, string courseName, string courseShortName, string yearID, string LectureID)
        {
            return from course in db.Courses
                   where ((course.CourseID == courseID || courseID == GlobalVar.KITURONG_QUERY)
                       && (course.CourseName == courseName || courseName == GlobalVar.KITURONG_QUERY)
                       && (course.CourseShortName == courseShortName || courseShortName == GlobalVar.KITURONG_QUERY)
                       && (course.YearID == yearID || yearID == GlobalVar.KITURONG_QUERY)
                       && (course.LecturerID == LectureID || LectureID == GlobalVar.KITURONG_QUERY))
                   select course;
        }
        public IQueryable<Lecturer> Select_Lecturer(string lectureID, string lectureName)
        {
            return from lecturer in db.Lecturers
                   where ((lecturer.LecturerID == lectureID || lectureID == GlobalVar.KITURONG_QUERY)
                       && (lecturer.LecturerName == lectureName || lectureName == GlobalVar.KITURONG_QUERY))
                   select lecturer;
        }
        public IQueryable<Student> Select_Student(string studentID, string firstName, string middleName, string lastName, string classID)
        {
            return from student in db.Students
                   where ((student.StudentID == studentID || studentID == GlobalVar.KITURONG_QUERY)
                       && (student.FirstName == firstName || firstName == GlobalVar.KITURONG_QUERY)
                       && (student.MiddleName == middleName || middleName == GlobalVar.KITURONG_QUERY)
                       && (student.LastName == lastName || lastName == GlobalVar.KITURONG_QUERY)
                       && (student.ClassID == classID || classID == GlobalVar.KITURONG_QUERY))
                   select student;
        }
        public IQueryable<Year> Select_Year(string yearID, string year, string semester)
        {
            return from nam in db.Years
                   where ((nam.YearID == yearID || yearID == GlobalVar.KITURONG_QUERY)
                       && (nam.Year1 == year || year == GlobalVar.KITURONG_QUERY)
                       && (nam.Semester == semester || semester == GlobalVar.KITURONG_QUERY))
                   select nam;

        }
        #endregion

        #region Function Add to Database
        public void AddCertificate(Certificate certificate)
        {         
            db.AddToCertificates(certificate);
            db.SaveChanges();
        }
        public void AddClass(Class classes)
        {
            db.AddToClasses(classes);
            db.SaveChanges();
        }
        public void AddCourse(Course course)
        {
            db.AddToCourses(course);
            db.SaveChanges();
        }
        public void AddLecturer(Lecturer lecture)
        {
            db.AddToLecturers(lecture);
            db.SaveChanges();
        }
        public void AddStudent(Student student)
        {
            db.AddToStudents(student);
            db.SaveChanges();
        }
        public void AddYear(Year year)
        {
            db.AddToYears(year);
            db.SaveChanges();
        }
        #endregion

        #region manager
        public void Edit_LectureTable(string[] Name)
        {
            try
            {
                int i = 0;
                foreach (var item in SelectAll_Lecturer())
                {
                   item.LecturerName = Name[i];
                   i++;
                }
                db.SaveChanges();
            }
            catch(Exception ex)
            {
                throw new ApplicationException("Có lỗi xảy ra khi lưu thay đổi", ex);
            }
        }
        public void Edit_ClassTable(string[] Name,string[] Year)
        {
            try
            {
                int i = 0;
                foreach (var item in SelectAll_Class())
                {
                    item.ClassName = Name[i];
                    item.ClassYear = Year[i];
                    i++;
                }
                db.SaveChanges();
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Có lỗi xảy ra khi lưu thay đổi", ex);
            }
        }
        public void Edit_YearTable(string[] Year, string[] Semester)
        {
            try
            {
                int i = 0;
                foreach (var item in SelectAll_Year())
                {
                    item.Year1 = Year[i];
                    item.Semester = Semester[i];
                    i++;
                }
                db.SaveChanges();
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Có lỗi xảy ra khi lưu thay đổi", ex);
            }
        }
        public void Edit_StudentTable(string[] firstName,string[] middleName,string[] lastName, bool[] gender, DateTime[] birthDay,string[] className)
        {
            try
            {
                int i = 0;
                foreach (var item in SelectAll_Student())
                {
                    item.FirstName = firstName[i];
                    item.MiddleName = middleName[i];
                    item.LastName = lastName[i];
                    item.Gender = gender[i];
                    item.Birthday = birthDay[i];
                    item.Class.ClassName = className[i];
                    i++;
                }
                db.SaveChanges();
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Có lỗi xảy ra khi lưu thay đổi", ex);
            }
        }
        public void Edit_CertificateTable(string[] Name, string[] courseName, string[] Score, string[] Year, string[] Semester)
        {
            try
            {
                int i = 0;
                foreach (var item in SelectAll_Certificate())
                {
                    string[] sSplitName = Name[i].Split(' ');
                    string middleName = "";
                    item.Student.FirstName = sSplitName[sSplitName.Length-1];
                    for (int j = 1; j < sSplitName.Length - 1; j++)
                        middleName += sSplitName[j] + " ";
                    item.Student.MiddleName = middleName.Trim();
                    item.Student.LastName = sSplitName[0];
                    item.Course.CourseName = courseName[i];
                    item.Score = Score[i];
                    item.Course.Year.Year1 = Year[i];
                    item.Course.Year.Semester = Semester[i];
                    i++;
                }
                db.SaveChanges();
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Có lỗi xảy ra khi lưu thay đổi", ex);
            }
        }
        public void Edit_CourseTable(string[] courseName, string[] courseShortName, string[] Year, string[] Semester, string[] lecturerName)
        {
            try
            {
                int i = 0;
                foreach (var item in SelectAll_Courses())
                {
                    item.CourseName = courseName[i];
                    item.CourseShortName = courseShortName[i];
                    item.Year.Year1 = Year[i];
                    item.Year.Semester = Semester[i];
                    item.Lecturer.LecturerName = lecturerName[i];
                    i++;
                }
                db.SaveChanges();
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Có lỗi xảy ra khi lưu thay đổi", ex);
            }
        }
        #endregion
    }
        
}
